Updating Data in a View

Article
10/23/2006

You update data in a view just as you would update data in a table. With a view you can also update the view's base tables. Views are, by default, buffered with optimistic row buffering. You can change this to table buffering; for more information on buffering, see Programming for Shared Access.

You can update data in a view through the interface or the language. The first step in updating view data is to make the view updateable. In most cases, the default property settings automatically prepare the view to be updateable, but updates are not sent to the data source until you instruct Visual FoxPro to do so by setting the SendUpdates property to On.

A view uses five properties to control updates. These properties are listed here with their default settings:

View Update Properties and Default Settings

View Property	Default Setting
Tables	Includes all tables that have updateable fields and have at least one primary key field.
KeyField	Database key fields and remote primary keys on the table.
UpdateName	Table_name.column_name for all fields.
Updateable	All fields except the primary key fields.
SendUpdates	Defaults to the session default, which is originally set to false (.F.); if you change it to true (.T.), that becomes the default for all views created in the session.
CompareMemo	Defaults to true (.T.), means that memo fields are included in the WHERE clause and are used for detecting update conflicts.

While all five properties are required to update data, the SendUpdates property serves as a "master switch" controlling whether or not updates are sent. As you develop your application, you might set the SendUpdates property off and then configure the other properties to enable updates to the fields you want updated. When you're ready to test your application, you can set the SendUpdates property on to start updates flowing.

In some more complex situations, the default update settings may not provide updates for a view you create through the language. To enable updates, look at the default settings for each of the update properties and adjust them as needed. You can also specify additional properties, such as UpdateType, WhereType, and so on, according to your preferences. For a complete list of view properties, see DBGETPROP( ).

To make a view updateable from the View Designer

In the View Designer, select the Update Criteria tab and verify the default settings.

The default settings for views you create through the View Designer usually prepare the view to be updateable; you only need to select the Send SQL Updates check box to turn updates on. You can further modify the tables, fields, SQL WHERE clause, and Update options as you desire.

To make a view updateable by setting view update properties

Examine the current default settings with the DISPLAY DATABASE command, and then modify properties for the view definition as you desire with the DBSETPROP( ) function.

The following example lists the steps you would follow to specify the five view update properties programmatically:

Note The default View properties may supply all the information needed to update your view.

Set the Tables property with at least one table name.

For example, if you have a view based on the customer table called cust_view, you could set the table name with the following function:
```
DBSETPROP('cust_view','View','Tables','customer')
```
Tip If a table appears as a qualifier in the UpdateName property but is not included in the default list for the Tables property, the table might not have a primary key field specified. Make the table updateable by adding the field you consider to be a key field to the KeyField property list, and then add the table to the Tables property list.
Set the KeyField property with one or more local Visual FoxPro field names that together define a unique key for the update table.

Using the same example, you could make cust_id the key field using the following code:
```
DBSETPROP('cust_view.cust_id','Field','KeyField',.T.)
```
Caution Be sure the key field(s) you specify define a unique key both in the base table you want to update and in the view.
Map the view fields to their base table fields with the UpdateName property. This property is particularly useful when your view is based on a join of two tables with a common field name, or when the fields are aliased in the view. To update the desired base table, you map the Visual FoxPro view field name to the base table field and table name.
```
DBSETPROP('cust_view.cust_id','Field','UpdateName',;
         'customer.cust_id')
```
Tip To avoid creating synonym fields in your view, you can qualify field names in the SQL statement you use to build your view. Then use the Visual FoxPro UpdateName property of the view to map each qualified field to the correct base table and field name.
Specify the scope of fields you want to update with the Updateable property. You should specify only those fields also specified with the UpdateName property.
```
DBSETPROP('cust_view.cust_id','Field','Updateable',;
         .T.)
```
Set the SendUpdates property to true (.T.). This is the master switch that instructs Visual FoxPro to create and send updates to any of the tables and fields you've specified as updateable.
```
DBSETPROP('cust_view','View','SendUpdates',.T.)
```

When you use DBSETPROP( ) to set properties on a view before you use the view, the settings are stored in the database and are used automatically whenever you activate the view. Once the view is active, you can use CURSORSETPROP( ) to change property settings on the active view. Property settings you set on an active view with CURSORSETPROP( ) are not saved when you close the view.

Updating Data in a View

See Also

Additional resources